﻿using System;
using System.Collections.Generic;
using System.Text;
using SAServices.Model;
using System.Data;

namespace SAServices.DAL
{
    public class FamilyDAL
    {
        internal static bool AddFamilyInfo(Model.ConnectionConext connCtx, Model.FamilyInfo familyInfo)
        {
            string sql = "INSERT INTO Families (ID, CategoryID, VendorID, StandardID, Name, DisplayName, Idx, IsCached) Values('{0}','{1}', {2}, {3}, '{4}','{5}', {6}, {7}) ";
            sql = String.Format(sql,
                familyInfo.ID,
                familyInfo.CategoryID,
                familyInfo.VendorID,
                familyInfo.StandardID,
                familyInfo.Name,
                familyInfo.DisplayName,
                familyInfo.Idx,
                familyInfo.IsCached?"1":"0");

            return GenDAL.ExecuteNonQuery(connCtx, sql);
        }

        internal static bool UpdateFamilyInfo(Model.ConnectionConext connCtx, Model.FamilyInfo familyInfo)
        {
            string sql = "UPDATE Families SET CategoryID = '{1}', VendorID = {6}, StandardID = {7}, Name = '{2}', DisplayName = '{3}', Idx = {4}, IsCached = {5} WHERE ID = '{0}';";
            sql = String.Format(sql,
                familyInfo.ID,
                familyInfo.CategoryID,
                familyInfo.Name,
                familyInfo.DisplayName,
                familyInfo.Idx,
                familyInfo.IsCached?"1":"0",
                familyInfo.VendorID,
                familyInfo.StandardID);

            return GenDAL.ExecuteNonQuery(connCtx, sql);
        }

        internal static bool DeleteFamilyInfo(Model.ConnectionConext connCtx, Guid familyId)
        {
            string sql = "DELETE FROM Families WHERE ID = '{0}'";
            sql = String.Format(sql, familyId);

            return GenDAL.ExecuteNonQuery(connCtx, sql);
        }

        internal static List<FamilyInfo> GetFamiliesByCategoryId(ConnectionConext connCtx, Guid cateId)
        {
            List<FamilyInfo> retList = new List<FamilyInfo>();
            
            string sql = "SELECT * FROM Families WHERE CategoryID = '{0}'";
            sql = string.Format(sql, cateId);

            DataTable dt = GenDAL.GetTable(connCtx, sql);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                FamilyInfo retFamInfo = new FamilyInfo();
                retFamInfo.ID = new Guid(Convert.ToString(dt.Rows[i]["ID"]));
                retFamInfo.Name = Convert.ToString(dt.Rows[i]["Name"]);
                retFamInfo.DisplayName = Convert.ToString(dt.Rows[i]["DisplayName"]);
                retFamInfo.VendorID = Convert.ToInt32(dt.Rows[i]["VendorID"]);
                retFamInfo.StandardID = Convert.ToInt32(dt.Rows[i]["StandardID"]);
                retFamInfo.CategoryID = cateId;
                retFamInfo.Idx = Convert.ToInt32(dt.Rows[i]["Idx"]);
                retFamInfo.IsCached = Convert.ToBoolean(dt.Rows[i]["IsCached"]);
                retFamInfo.PublishDate = Convert.ToDateTime(dt.Rows[i]["PublishDate"]);

                retList.Add(retFamInfo);
            }

            return retList;
        }

        internal static List<FamilyInfo> GetFamiliesByCategoryIdnew(ConnectionConext connCtx, Guid cateId, string str, string searchString)
        {
            List<FamilyInfo> retList = new List<FamilyInfo>();


            string Sql = str;
            string sql = "  SELECT * FROM Families WHERE   (  " + Sql.Remove(Sql.LastIndexOf("or"))+")";
            if (searchString != "")
             sql = sql + " and Name like '%{2}%'";
            sql = string.Format(sql, cateId, str, searchString);

            DataTable dt = GenDAL.GetTable(connCtx, sql);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                FamilyInfo retFamInfo = new FamilyInfo();
                retFamInfo.ID = new Guid(Convert.ToString(dt.Rows[i]["ID"]));
                retFamInfo.Name = Convert.ToString(dt.Rows[i]["Name"]);
                retFamInfo.DisplayName = Convert.ToString(dt.Rows[i]["DisplayName"]);
                retFamInfo.VendorID = Convert.ToInt32(dt.Rows[i]["VendorID"]);
                retFamInfo.StandardID = Convert.ToInt32(dt.Rows[i]["StandardID"]);
                retFamInfo.CategoryID = cateId;
                retFamInfo.Idx = Convert.ToInt32(dt.Rows[i]["Idx"]);
                retFamInfo.IsCached = Convert.ToBoolean(dt.Rows[i]["IsCached"]);
                retFamInfo.PublishDate = Convert.ToDateTime(dt.Rows[i]["PublishDate"]);

                retList.Add(retFamInfo);
            }

            return retList;

        }

        internal static List<FamilyInfo> GetFamiliesByVendorId(ConnectionConext connCtx, int vendorId, string searchString)
        {
            List<FamilyInfo> retList = new List<FamilyInfo>();

            string sql = "SELECT * FROM Families WHERE VendorID = {0}";
            if (searchString != "")
                sql = sql + " and Name like '%{1}%'";
            sql = string.Format(sql, vendorId,searchString);

            DataTable dt = GenDAL.GetTable(connCtx, sql);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                FamilyInfo retFamInfo = new FamilyInfo();
                retFamInfo.ID = new Guid(Convert.ToString(dt.Rows[i]["ID"]));
                retFamInfo.Name = Convert.ToString(dt.Rows[i]["Name"]);
                retFamInfo.DisplayName = Convert.ToString(dt.Rows[i]["DisplayName"]);
                retFamInfo.CategoryID = new Guid(Convert.ToString(dt.Rows[i]["CategoryID"]));
                retFamInfo.VendorID = vendorId;
                retFamInfo.StandardID = Convert.ToInt32(dt.Rows[0]["StandardID"]);
                retFamInfo.Idx = Convert.ToInt32(dt.Rows[i]["Idx"]);
                retFamInfo.IsCached = Convert.ToBoolean(dt.Rows[i]["IsCached"]);
                retFamInfo.PublishDate = Convert.ToDateTime(dt.Rows[i]["PublishDate"]);

                retList.Add(retFamInfo);
            }

            return retList;
        }

        internal static List<FamilyInfo> GetFamiliesByStandardId(ConnectionConext connCtx, int standardId)
        {
            List<FamilyInfo> retList = new List<FamilyInfo>();

            string sql = "SELECT * FROM Families WHERE StandardID = {0}";
            sql = string.Format(sql, standardId);

            DataTable dt = GenDAL.GetTable(connCtx, sql);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                FamilyInfo retFamInfo = new FamilyInfo();
                retFamInfo.ID = new Guid(Convert.ToString(dt.Rows[i]["ID"]));
                retFamInfo.Name = Convert.ToString(dt.Rows[i]["Name"]);
                retFamInfo.DisplayName = Convert.ToString(dt.Rows[i]["DisplayName"]);
                retFamInfo.CategoryID = new Guid(Convert.ToString(dt.Rows[i]["CategoryID"]));
                retFamInfo.VendorID = Convert.ToInt32(dt.Rows[0]["VendorID"]);
                retFamInfo.StandardID = standardId;
                retFamInfo.Idx = Convert.ToInt32(dt.Rows[i]["Idx"]);
                retFamInfo.IsCached = Convert.ToBoolean(dt.Rows[i]["IsCached"]);
                retFamInfo.PublishDate = Convert.ToDateTime(dt.Rows[i]["PublishDate"]);

                retList.Add(retFamInfo);
            }

            return retList;
        }

        internal static List<FamilyInfo> GetFamiliesByCategoryVendorId(ConnectionConext connCtx, Guid cateId, int vendorId, string searchString,string str)
        {
            List<FamilyInfo> retList = new List<FamilyInfo>();
            string Sql=str;
            string sql = "SELECT * FROM Families WHERE   VendorID = {1} AND (" + Sql.Remove(Sql.LastIndexOf("or"))+")";
            if (searchString != "")
            sql = sql + " and Name like '%{2}%'";
            sql = string.Format(sql, cateId, vendorId,searchString);
           
                

            DataTable dt = GenDAL.GetTable(connCtx, sql);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                FamilyInfo retFamInfo = new FamilyInfo();
                retFamInfo.ID = new Guid(Convert.ToString(dt.Rows[i]["ID"]));
                retFamInfo.Name = Convert.ToString(dt.Rows[i]["Name"]);
                retFamInfo.DisplayName = Convert.ToString(dt.Rows[i]["DisplayName"]);
                retFamInfo.CategoryID = cateId;
                retFamInfo.VendorID = vendorId;
                retFamInfo.StandardID = Convert.ToInt32(dt.Rows[0]["StandardID"]);
                retFamInfo.Idx = Convert.ToInt32(dt.Rows[i]["Idx"]);
                retFamInfo.IsCached = Convert.ToBoolean(dt.Rows[i]["IsCached"]);
                retFamInfo.PublishDate = Convert.ToDateTime(dt.Rows[i]["PublishDate"]);

                retList.Add(retFamInfo);
            }

            return retList;
        }

        internal static List<FamilyInfo> SearchFamilies(ConnectionConext connCtx, string searchString)
        {
            List<FamilyInfo> retList = new List<FamilyInfo>();
            string sql = "";
            if (searchString == "")
            {
                sql = "SELECT * FROM Families";
            }
            else
            {
                sql = "SELECT * FROM Families WHERE Name like '%{0}%'";
                sql = string.Format(sql, searchString);
            }
           


            DataTable dt = GenDAL.GetTable(connCtx, sql);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                FamilyInfo retFamInfo = new FamilyInfo();
                retFamInfo.ID = new Guid(Convert.ToString(dt.Rows[i]["ID"]));
                retFamInfo.Name = Convert.ToString(dt.Rows[i]["Name"]);
                retFamInfo.DisplayName = Convert.ToString(dt.Rows[i]["DisplayName"]);
                retFamInfo.CategoryID = new Guid(Convert.ToString(dt.Rows[i]["CategoryID"]));
                retFamInfo.VendorID = Convert.ToInt32(dt.Rows[0]["VendorID"]);
                retFamInfo.StandardID = Convert.ToInt32(dt.Rows[0]["StandardID"]);
                retFamInfo.Idx = Convert.ToInt32(dt.Rows[i]["Idx"]);
                retFamInfo.IsCached = Convert.ToBoolean(dt.Rows[i]["IsCached"]);
                retFamInfo.PublishDate = Convert.ToDateTime(dt.Rows[i]["PublishDate"]);

                retList.Add(retFamInfo);
            }

            return retList;
        }


        internal static List<FamilyInfo> SearchFamiliesByVendor(ConnectionConext connCtx, string searchString,int vendorid)
        {
            List<FamilyInfo> retList = new List<FamilyInfo>();
            string sql = "";
            if (searchString == "")
            {
                sql = "SELECT * FROM Families";
            }
            else
            {
                sql = "SELECT * FROM Families WHERE Name like '%{0}%' and VendorID = {1}";
                sql = string.Format(sql, searchString, vendorid);
            }


            DataTable dt = GenDAL.GetTable(connCtx, sql);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                FamilyInfo retFamInfo = new FamilyInfo();
                retFamInfo.ID = new Guid(Convert.ToString(dt.Rows[i]["ID"]));
                retFamInfo.Name = Convert.ToString(dt.Rows[i]["Name"]);
                retFamInfo.DisplayName = Convert.ToString(dt.Rows[i]["DisplayName"]);
                retFamInfo.CategoryID = new Guid(Convert.ToString(dt.Rows[i]["CategoryID"]));
                retFamInfo.VendorID = Convert.ToInt32(dt.Rows[0]["VendorID"]);
                retFamInfo.StandardID = Convert.ToInt32(dt.Rows[0]["StandardID"]);
                retFamInfo.Idx = Convert.ToInt32(dt.Rows[i]["Idx"]);
                retFamInfo.IsCached = Convert.ToBoolean(dt.Rows[i]["IsCached"]);
                retFamInfo.PublishDate = Convert.ToDateTime(dt.Rows[i]["PublishDate"]);

                retList.Add(retFamInfo);
            }

            return retList;
        }

        internal static FamilyInfo GetFamilyInfo(ConnectionConext connCtx, Guid familyId)
        {
            FamilyInfo retFamInfo = new FamilyInfo();

            string sql = "SELECT * FROM Families WHERE ID = '{0}'";
            sql = string.Format(sql, familyId);

            DataTable dt = GenDAL.GetTable(connCtx, sql);

            if (dt.Rows.Count > 0)
            {
                retFamInfo.ID = new Guid(Convert.ToString(dt.Rows[0]["ID"]));
                retFamInfo.Name = Convert.ToString(dt.Rows[0]["Name"]);
                retFamInfo.DisplayName = Convert.ToString(dt.Rows[0]["DisplayName"]);
                retFamInfo.CategoryID = new Guid(Convert.ToString(dt.Rows[0]["CategoryID"]));
                retFamInfo.VendorID = Convert.ToInt32(dt.Rows[0]["VendorID"]);
                retFamInfo.StandardID = Convert.ToInt32(dt.Rows[0]["StandardID"]);
                retFamInfo.Idx = Convert.ToInt32(dt.Rows[0]["Idx"]);
                retFamInfo.IsCached = Convert.ToBoolean(dt.Rows[0]["IsCached"]);
                retFamInfo.PublishDate = Convert.ToDateTime(dt.Rows[0]["PublishDate"]);
            
            }

            return retFamInfo;
        }

        internal static bool UpdateFamilyCachedFlag(ConnectionConext connCtx, Guid familyId, bool bCached)
        {
            string sql = "UPDATE Families SET IsCached = {1} WHERE ID = '{0}';";
            sql = String.Format(sql,
                familyId,
                bCached ? "1":"0");

            return GenDAL.ExecuteNonQuery(connCtx, sql);
        }
    }
}
